////////////////////////////////////////////////////////////////////////////////
//
//  FLYING KIWI BV
//  Copyright 2010 Flying Kiwi BV
//  All Rights Reserved.
//
//  NOTICE: Flying Kiwi permits you to use, modify, and distribute this file
//  in accordance with the terms of the license agreement accompanying it.
//
////////////////////////////////////////////////////////////////////////////////
package plantool.modules.orders {
	//--------------------------------------------------------------------------
	//  Imports
	//--------------------------------------------------------------------------
	import flash.events.Event;
	import flash.utils.describeType;
	
	import kiwi.utils.Validation;
	
	import plantool.core.CoreApi;
	import plantool.data.Field;
	import plantool.data.FieldTypes;
	import plantool.data.QueryResult;
	import plantool.data.Table;
	import plantool.events.OrderEvent;
	import plantool.events.OrderrowEvent;
	import plantool.modules.menu.data.MenuGroupData;
	import plantool.modules.menu.data.MenuItemData;
	import plantool.modules.orders.data.Order;
	import plantool.modules.orders.data.Ordermeta;
	import plantool.modules.orders.data.Orderplanning;
	import plantool.modules.orders.data.Orderrow;
	import plantool.modules.orders.windows.OrderListWindow;
	import plantool.modules.orders.windows.OrderWindow;
	import plantool.settings.Setting;
	import plantool.settings.Settings;
	
	//--------------------------------------------------------------------------
	//  [Metadata (Events, Style, Effects etc)]
	//--------------------------------------------------------------------------
	[Event(name="orderrowplanning_created", type="plantool.events.OrderrowEvent")]
	[Event(name="orderrowplanning_updated", type="plantool.events.OrderrowEvent")]
	[Event(name="order_created", type="plantool.events.OrderEvent")]
	
	//--------------------------------------------------------------------------
	//  Class declaration
	//--------------------------------------------------------------------------
	public class OrderApi extends CoreApi {
		//--------------------------------------------------------------------------
		//Class constants
		//--------------------------------------------------------------------------
		private static const NUM_TABLES:uint				= 4;
		
		//--------------------------------------------------------------------------
		//Class variables
		//--------------------------------------------------------------------------
		//TODO: Add isWorking
		private static var _instance:OrderApi		= null;
		private var _tblRdyCount:uint				= 0;
		private var _orderTable:Table				= null;
		private var _orderRowTable:Table			= null;
		private var _orderPlanningTable:Table		= null;
		private var _orderMetaTable:Table			= null;
		private var _orderClass:Class				= Order;
		private var _orderrowClass:Class			= Orderrow;
		private var _orders:Vector.<Order>			= new Vector.<Order>();
		
		//--------------------------------------------------------------------------
		//Class properties
		//--------------------------------------------------------------------------
		public function get orderTable():Table {
			return _orderTable;
		}
		
		public function get orderMetaTable():Table { return _orderMetaTable; }
		
		public function get orderClass():Class {
			return _orderClass;
		}
		
		public function get orderrowClass():Class {
			return _orderrowClass;
		}
		
		//--------------------------------------------------------------------------
		//Constructor and Destructor
		//--------------------------------------------------------------------------
		public function OrderApi(pvt:PrivateConstructorClass) {
			super();
			
			_orderTable = new Table('orders');
			_orderTable.addField(new Field('onr', FieldTypes.TEXT));
			
			_orderRowTable = new Table('orderrows');
			_orderRowTable.addField(new Field('order_id', FieldTypes.INT));
			_orderRowTable.addField(new Field('time', FieldTypes.FLOAT));
			
			_orderPlanningTable = new Table("order_planning");
			_orderPlanningTable.addField(new Field("order_id"			, FieldTypes.INT));
			_orderPlanningTable.addField(new Field("orderrow_id"		, FieldTypes.INT));
			_orderPlanningTable.addField(new Field("related_id"			, FieldTypes.INT));
			_orderPlanningTable.addField(new Field("timestamp"			, FieldTypes.DATE));
			_orderPlanningTable.addField(new Field("time"				, FieldTypes.FLOAT));
			_orderPlanningTable.addField(new Field("ready"				, FieldTypes.BOOLEAN));
			
			_orderMetaTable = new Table("order_meta");
			_orderMetaTable.addField(new Field("order_id"			, FieldTypes.INT));
			_orderMetaTable.addField(new Field(Ordermeta.STRING_FIELD_1	, FieldTypes.VARCHAR));
			_orderMetaTable.addField(new Field(Ordermeta.STRING_FIELD_2	, FieldTypes.VARCHAR));
			_orderMetaTable.addField(new Field(Ordermeta.BOOL_FIELD_1	, FieldTypes.BOOLEAN));
			_orderMetaTable.addField(new Field(Ordermeta.BOOL_FIELD_2	, FieldTypes.BOOLEAN));
			
			//add menu
			var group:MenuGroupData = new MenuGroupData();
			group.title				= "Orders";
			group.addItem(new MenuItemData("Overzicht", showListForm));
			group.addItem(new MenuItemData("Toevoegen", showForm));
			menuApi.addGroup(group);
		}
		
		//--------------------------------------------------------------------------
		//Class methods
		//--------------------------------------------------------------------------
		private function createOrderrowPlanQuery(row:Orderrow, date:Date, related_id:uint):String {
			return "INSERT INTO `order_planning`(`order_id`, `orderrow_id`, `related_id`, `timestamp`, `time`, `ready`) " +
				"VALUES(" + 
				row.order_id + ", " + 
				row.id + ", " +
				related_id + ", " +
				databaseManager.formatDate(date) + ", " +
				row.remaining + ", " +
				false + 
				")";
		}
		
		private function createOrderrowPlanning(row:Orderrow, date:Date, related_id:uint, onCompleteHandler:Function, onErrorHandler:Function, userdata:* = null):void {
			//create onCompleteHandler
			var onComplete_createPlanning:Function = function(result:QueryResult):void {
				//dispatch event
				dispatchEvent(new OrderrowEvent(OrderrowEvent.ORDERROWPLANNING_CREATED, row.order_id, row.id, related_id, date));
				
				//return to caller
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//create user hour record
			databaseManager.query(createOrderrowPlanQuery(row, date, related_id), onComplete_createPlanning, onErrorHandler, userdata);
		}
		
		private function createOrder(order:Order, onCompleteHandler:Function, onErrorHandler:Function):void {
			//create var for storing userid
			var newOrderID:int = 0;
			
			//create complete handler
			var onComplete_createOrderrows:Function = function(result:QueryResult):void {
				//dispatch event
				dispatchEvent(new OrderEvent(OrderEvent.ORDER_CREATED, newOrderID));
				
				//get new users
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//create orderrow querys
			//TODO:Generalize the creation insert-querys
			var onComplete_createOrder:Function = function(result:QueryResult):void {
				//validate result
				if (Validation.isSet(result) && Validation.isSet(result.insertRowId)) {
					newOrderID = result.insertRowId;
				
					//create querys array for transaction
					var querys:Array = new Array();
					
					for (var j:uint = 0; j < order.orderrows.length; j++) {
						//get the row and set order-id
						var row:Orderrow 	= order.orderrows[j];
						row.order_id		= newOrderID;
						
						//create var list
						varList = flash.utils.describeType(row)..accessor;
						
						var rowfields:String = "(";
						var rowvalues:String = " VALUES(";
						
						cnt = 0;
						for (i = 0; i < varList.length(); i++) {
							//skip the field 'id'
							//TODO: Create array for fields to skip
							if (varList[i].@name != "id" && varList[i].@name != "meta" && varList[i].@name != "orderrows" && varList[i].@name != "base" && varList[i].@name != "rowsLoaded" && varList[i].@name != "typeOf") {
								//add field separator
								if (cnt > 0) {
									rowfields += ", ";
									rowvalues += ", ";
								}
								
								rowfields += "`" + varList[i].@name + "`";
								rowvalues += "'" + row[varList[i].@name] + "'";
								
								//increase field counter
								cnt++;
							}
						}
						
						//close query values and fields
						rowfields += ")";
						rowvalues += ")";
						
						//create query
						querys.push("INSERT INTO `orderrows`" + rowfields + rowvalues);
					}
					
					//create query for meta
					varList = flash.utils.describeType(order.meta)..accessor;
					order.meta.order_id = newOrderID;
					
					var metafields:String = "(";
					var metavalues:String = " VALUES(";
					
					cnt = 0;
					for (i = 0; i < varList.length(); i++) {
						//skip the field 'id'
						//TODO: Create array for fields to skip
						if (varList[i].@name != "id" && varList[i].@name != "typeOf") {
							//add field separator
							if (cnt > 0) {
								metafields += ", ";
								metavalues += ", ";
							}
							
							metafields += "`" + varList[i].@name + "`";
							metavalues += "'" + order.meta[varList[i].@name] + "'";
							
							//increase field counter
							cnt++;
						}
					}
					
					//close query values and fields
					metafields += ")";
					metavalues += ")";
					
					//create query
					querys.push("INSERT INTO `order_meta`" + metafields + metavalues);
					
					//start the transaction for the orderrows
					databaseManager.transaction(querys, onComplete_createOrderrows, onErrorHandler);
				} else {
					trace("orderApi::createOrder->onComplete_createOrder - result or insertRowId not set");
				}
			}
			
			//get all field to save to database
			var varList:XMLList = flash.utils.describeType(order)..accessor;
			
			//create the insert query
			//TODO:Generalize the creation insert-querys
			var fields:String	= "(";
			var values:String	= " VALUES(";
			var cnt:uint		= 0;
			for (var i:uint = 0; i < varList.length(); i++) {
				//skip the field 'id'
				//TODO: Create array for fields to skip
				if (varList[i].@name != "id" && varList[i].@name != "meta" && varList[i].@name != "orderrows" && varList[i].@name != "base" && varList[i].@name != "rowsLoaded" && varList[i].@name != "typeOf") {
					//add field separator
					if (cnt > 0) {
						fields += ", ";
						values += ", ";
					}
					
					fields += "`" + varList[i].@name + "`";
					values += "'" + order[varList[i].@name] + "'";
					
					//increase field counter
					cnt++;
				}
			}
			
			//close query values and fields
			fields += ")";
			values += ")";
			
			//create query
			var query:String = "INSERT INTO `orders`" + fields + values;
			
			//create the user
			//TODO: remove order when order-rows fail
			databaseManager.query(query, onComplete_createOrder, onErrorHandler);
		}
		
		private function initSettings():void {
			//get settings
			var labelStringField1:Setting 	= new Setting();
			labelStringField1.setting_id	= Ordermeta.STRING_FIELD_1;
			labelStringField1.value			= "Klantnaam";
			labelStringField1.type			= Settings.TYPE_STRING;
			settings.initSetting(labelStringField1);
			
			//get settings
			var labelStringField2:Setting 	= new Setting();
			labelStringField2.setting_id	= Ordermeta.STRING_FIELD_2;
			labelStringField2.value			= "Leverdatum";
			labelStringField2.type			= Settings.TYPE_STRING;
			settings.initSetting(labelStringField2);
			
			//get settings
			var labelBoolField1:Setting 	= new Setting();
			labelBoolField1.setting_id		= Ordermeta.BOOL_FIELD_1;
			labelBoolField1.value			= "Spoed";
			labelBoolField1.type			= Settings.TYPE_STRING;
			settings.initSetting(labelBoolField1);
			
			//get settings
			var labelBoolField2:Setting 	= new Setting();
			labelBoolField2.setting_id		= Ordermeta.BOOL_FIELD_2;
			labelBoolField2.value			= "Nieuw product";
			labelBoolField2.type			= Settings.TYPE_STRING;
			settings.initSetting(labelBoolField2);
		}
		
		private function onComplete_createTable():void {
			//table ready
			_tblRdyCount++;
			
			//all tables ready?
			if (_tblRdyCount == NUM_TABLES) {
				//init settings
				initSettings();
				
				//inform listeners that we're complete
				dispatchEvent(new Event(Event.COMPLETE));
			}
		}
		
		private function updateOrder(order:Order, onCompleteHandler:Function, onErrorHandler:Function):void {
			//create complete handler
			var onComplete_createOrder:Function = function(result:QueryResult):void {
				//get new users
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//get all field to save to database
			var varList:XMLList = flash.utils.describeType(order)..accessor;
			
			//create the update query
			//TODO:Generalize the creation update-querys
			var values:String	= "";
			var cnt:uint		= 0;
			for (var i:uint = 0; i < varList.length(); i++) {
				//skip the field 'id'
				if (varList[i].@name != "id") {
					//add field separator
					if (cnt > 0) {
						values += ", ";
					}
					
					//set values
					values += "`" + varList[i].@name + "`='" + order[varList[i].@name] + "'";
					
					//increase field counter
					cnt++;
				}
			}
			
			//create query
			var query:String = "UPDATE `orders` SET " + values + " WHERE `id`=" + order.id;
			
			//TODO: Update order-rows
			
			//create the user
			databaseManager.query(query, onComplete_createOrder, onErrorHandler);
		}
		
		public static function getInstance():OrderApi {
			//check if instance exists, if not create one
			if (!Validation.isSet(_instance)) {
				_instance = new OrderApi(new PrivateConstructorClass());
			}
			
			//return instance
			return _instance;
		}
		
		public function getOrderrows(order:Order, onCompleteHandler:Function, onErrorHandler:Function):void {
			//create oncomplete function to store users
			var onGet_orderrows:Function = function(result:QueryResult):void {
				//call oncomplete handler with result
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//get all orders
			var query:String = "SELECT *, (`orows`.`time` - (SELECT TOTAL(`time`) FROM `order_planning` `oplanning` WHERE `oplanning`.`orderrow_id`=`orows`.`id`)) as `remaining` FROM `orderrows` `orows` WHERE `orows`.`order_id` = " + order.id;
			databaseManager.query(query, onGet_orderrows, onErrorHandler, _orderrowClass);
		}
		
		public function getOrdermeta(order:Order, onCompleteHandler:Function, onErrorHandler:Function):void {
			//create oncomplete function to store users
			var onGet_ordermeta:Function = function(result:QueryResult):void {
				//call oncomplete handler with result
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			var query:String = "SELECT * FROM `order_meta` WHERE `order_id` = " + order.id;
			databaseManager.query(query, onGet_ordermeta, onErrorHandler, Ordermeta);
		}
		
		public function getOrders(onCompleteHandler:Function, onErrorHandler:Function):void {
			//create oncomplete function to store users
			var onGet_orders:Function = function(result:QueryResult):void {
				//call oncomplete handler with result
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//get all orders
			var query:String = "SELECT * FROM `orders` `o` WHERE `o`.`id` IN " +
				"(SELECT `order_id` FROM `orderrows` `orows` WHERE `orows`.`time` > (SELECT TOTAL(`time`) FROM `order_planning` `oplanning` WHERE `oplanning`.`orderrow_id`=`orows`.`id`)" +
				"GROUP BY `order_id`)";
			databaseManager.query(query, onGet_orders, onErrorHandler, _orderClass);
		}
		
		public function getOrderByID(oid:uint, onCompleteHandler:Function, onErrorHandler:Function):void {
			//create oncomplete function to store users
			var onGet_order:Function = function(result:QueryResult):void {
				//call oncomplete handler with result
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			
			//get all orders
			var query:String = "SELECT * FROM `orders` `o` WHERE `o`.`id` = " + oid.toString();
			databaseManager.query(query, onGet_order, onErrorHandler, _orderClass);
		}
		
		public override function install():void {
			//check database
			databaseManager.createTable(_orderTable, onComplete_createTable);
			databaseManager.createTable(_orderRowTable, onComplete_createTable);
			databaseManager.createTable(_orderPlanningTable, onComplete_createTable);
			databaseManager.createTable(_orderMetaTable, onComplete_createTable);
		}
		
		public function planOrderrow(row:Orderrow, date:Date, related_id:uint, onCompleteHandler:Function = null, onErrorHandler:Function = null, userdata:* = null):void {
			//create onCompleteHandler
			var onComplete_createPlanning:Function = function(result:QueryResult):void {
				//dispatch event to update hours
				
				//return to caller
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}

			//create the user hour
			createOrderrowPlanning(row, date, related_id, onComplete_createPlanning, onErrorHandler, userdata);
		}
		
		public function updateOrderrowPlanning(orderPlanning:Orderplanning, onCompleteHandler:Function = null, onErrorHandler:Function = null, userdata:* = null):void {
			var onComplete_updateOrderPlanning:Function = function(result:QueryResult):void {
				//dispatch event
				dispatchEvent(new OrderrowEvent(OrderrowEvent.ORDERROWPLANNING_UPDATED, orderPlanning.order_id, orderPlanning.id, orderPlanning.related_id, orderPlanning.fromDate, orderPlanning));
				
				//call oncomplete
				if (Validation.isSet(onCompleteHandler)) {
					onCompleteHandler(result);
				}
			}
			databaseManager.query("UPDATE `order_planning` SET `related_id` = " + orderPlanning.related_id + ", `timestamp` = " + databaseManager.formatDate(orderPlanning.fromDate) + ", `ready` = " + orderPlanning.ready + "  WHERE `id` = " + orderPlanning.id, onComplete_updateOrderPlanning, onErrorHandler);
		}
		
		public function saveOrder(order:Order, onCompleteHandler:Function, onErrorHandler:Function):void {
			//if id > 0 update the user, otherwise create a new user
			if (order.id > 0) {
				updateOrder(order, onCompleteHandler, onErrorHandler);
			} else {
				createOrder(order, onCompleteHandler, onErrorHandler);
			}
		}
		
		public function showForm(order:Order = null):void {
			var window:OrderWindow = new OrderWindow();
			window.show();
		}
		
		public function showListForm():void {
			OrderListWindow.getInstance().show();
		}
	}
}

//--------------------------------------------------------------------------
//END OF PACKAGE
//Define the PrivateConstructorClass here so an instance cannot be by anyone
//except for the class defined in the above package
//--------------------------------------------------------------------------
class PrivateConstructorClass {
	//--------------------------------------------------------------------------
	//Constructor and Destructor
	//--------------------------------------------------------------------------
	public function PrivateConstructorClass() {
	}
}